<?php
class Checkin {

    public function __construct() {
        $this->SQL = MySQL::getInstance();
    }

    /*
     * add - insert a record of a user climbing a route (a checkin)
     * @param userId int user id
     * @param data array checkin data of form array(route_id, completed, comment)
     * @return bool TRUE/FALSE
     */
    public function add($userId, $data) {
        if(!$userId || !isset($data['route_id']) || !$data['route_id'] || !isset($data['completed'])) {
            return FALSE;
        }
        //if route was completed with block, store # of blocks instead
        if($data['completed'] == 1) {
            $data['completed'] = $data['blocks'];
        }
        return $this->SQL->fquery('
            INSERT INTO users_routes_link
            (user_id, route_id, timestamp, completed, comment)
            VALUES
            (%d, %d, NOW(), %d, "%s")',
            $userId,
            $data['route_id'],
            $data['completed'],
            addslashes($data['comment'])
        );
    }

    /*
     * get - retrieve details about a checkin
     * @param id int checkin id
     * @return array of form (id, user, date, route, comment, completed)
     */
    public function get($id) {
        return $this->SQL->fetch_single('
            SELECT id, user_id, route_id, timestamp, completed, comment
            FROM users_routes_link
            WHERE id = %d
            LIMIT 1',
            $id
        );
    }

    /*
     * getLocationCount - get the total amount of checkins done by a user at this location
     * @param id int location id
     * @param userid int user id
     * @return int checkin count
     */
    public function getLocationCount($id, $userId) {
        return $this->SQL->fetch_value('
            SELECT COUNT(*)
            FROM users_routes_link url
            JOIN routes r
                ON r.id = url.route_id
                AND r.location_id = %d
            WHERE url.user_id = %d
            LIMIT 1',
            $id,
            $userId
        );
    }

    /*
     * getRouteCount - get the total amount of checkins a user has done on this route
     * @param id int route id
     * @param userid int user id
     * @return int checkin count
     */
    public function getRouteCount($id, $userId) {
        return $this->SQL->fetch_value('
            SELECT COUNT(*)
            FROM users_routes_link
            WHERE user_id = %d
            AND route_id = %d
            LIMIT 1',
            $userId,
            $id
        );
    }

    /*
     * getPreviousCheckin - get info about the second-newet checkin for a route for a user
     * @param routeId int route id
     * @param userId int user id
     * @return array of form (date, completed, comment)
     */
    public function getPreviousCheckin($routeId, $userId) {
        return $this->SQL->fetch_single('
            SELECT timestamp, completed, comment
            FROM users_routes_link
            WHERE route_id = %1$d
            AND user_id = %2$d
            AND id != (
                SELECT MAX(id)
                FROM users_routes_link
                WHERE route_id = %1$d
                AND user_id = %2$d
            )
            ORDER BY timestamp DESC
            LIMIT 1',
            $routeId,
            $userId
        );
    }
}
?>
